package JDBC_test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;


public class oracle {
	private final String DRIVER = "oracle.jdbc.driver.OracleDriver";// Oracle的驱动
	private final static String URL = "jdbc:oracle:thin:@192.168.128.129:1521:ORCL";// Oracle9i为Oracle的主机串名（也就是Oracle的SID）在装Oracle的时候填写的SID
	private final static String DBUSER = "mytest";// 这里就以scott/tiger为用户登录
	private final static String DBPASSWORD = "mytest";
	private static Connection con = null;// 预先定义一个连接接口
	static Statement statement=null;
	/*
	public App() {// 在构造方法中加载数据库驱动
		try {
			Class.forName(DRIVER).newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	 */
	public static Connection getConnection() { // 连接数据库的方法
		try {
			if(con==null) {
				con = DriverManager.getConnection(URL, DBUSER, DBPASSWORD);
			}
			
		} catch (Exception ew) {
			ew.printStackTrace();
		}

		return con;
	}

	public static JSONArray list(String sql) {
		System.out.println(sql);
		JSONArray result=new JSONArray();
		try {
			getConnection();
			if(statement==null) {
				statement=con.createStatement();
			}
			result = convertResultSetToJsonArr(statement.executeQuery(sql));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static int execute(String sql) {
		System.out.println(sql);
		int result=0;
		try {
			getConnection();
			if(statement==null) {
				statement=con.createStatement();
			}
			result = statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public static void txtToOracle() {
		File file = new File(System.getProperty("user.dir")+File.separator+"text1.txt");
	    BufferedReader reader = null;
	    JSONArray ja=new JSONArray();
	    try {
	        reader = new BufferedReader(new FileReader(file));
	        String tempStr;
	        while ((tempStr = reader.readLine()) != null) {
	        	String[] ss1=tempStr.split(" ");
	        	
	        	String c1=ss1[0];
	        	String c2=ss1[1];
	        	String c3=ss1[2];
	        	String[] ss2=ss1[3].split("-");
	        	String c5=ss1[4].equals("入")?"1":"2";
	        	String c6=ss1[5].equals("闭")?"1":"2";
	        	String c7=ss1[6].equals("赚")?"1":"2";
	        	for (int i = 0; i < ss2.length; i++) {
	        		String c4=ss2[i];
	        		ja=list("select \"id\",\"biaoshi\" from \"table1\" where \"id\"='"+c4+"'");
		        	if(ja.size()>0) {
		        		c4=ja.getJSONObject(0).getString("biaoshi");
		        	}
	        		String sql="INSERT INTO \"MYTEST\".\"t1\" VALUES ('"+UUID.randomUUID()+"', '"+c1+"', '"+c2+"', '"+c3+"', '"+c4+"', '"+c5+"', '"+c6+"', '"+c7+"')";
	        		execute(sql);
				}
	        }
	        reader.close();
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        if (reader != null) {
	            try {
	                reader.close();
	            } catch (Exception e1) {
	                e1.printStackTrace();
	            }
	        }
	    }
	}

	public static void main(String[] args) {
		txtToOracle();
	}

	public static JSONArray convertResultSetToJsonArr(ResultSet rs) throws SQLException, JSONException {
		JSONArray json = new JSONArray();
		ResultSetMetaData rsmd = rs.getMetaData();

		while (rs.next()) {
			int numColumns = rsmd.getColumnCount();
			JSONObject obj = new JSONObject();

			for (int i = 1; i < numColumns + 1; i++) {
				String column_name = rsmd.getColumnName(i);

				if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
					obj.put(column_name, rs.getArray(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
					obj.put(column_name, rs.getBoolean(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
					obj.put(column_name, rs.getBlob(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
					obj.put(column_name, rs.getDouble(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
					obj.put(column_name, rs.getFloat(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
					obj.put(column_name, rs.getNString(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
					obj.put(column_name, rs.getString(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
					obj.put(column_name, rs.getInt(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
					obj.put(column_name, rs.getDate(column_name));
				} else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
					obj.put(column_name, rs.getTimestamp(column_name));
				} else {
					obj.put(column_name, rs.getObject(column_name));
				}
			}

			json.add(obj);
		}

		return json;
	}
}
